// 查看数据库迁移状态脚本
const { Pool } = require('pg')

// 数据库连接配置
const pool = new Pool({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 5432,
  database: process.env.DB_NAME || 'aide',
  user: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD || 'unidt@123'
})

async function showMigrationStatus() {
  try {
    console.log('📊 数据库迁移状态\n')
    
    // 检查迁移表是否存在
    const tableExists = await pool.query(`
      SELECT EXISTS (
        SELECT FROM information_schema.tables 
        WHERE table_schema = 'public' 
        AND table_name = 'schema_migrations'
      )
    `)
    
    if (!tableExists.rows[0].exists) {
      console.log('❌ 迁移表不存在，请先运行迁移脚本')
      return
    }
    
    // 获取迁移历史
    const migrations = await pool.query(`
      SELECT 
        version,
        description,
        executed_at,
        execution_time_ms,
        success
      FROM schema_migrations 
      ORDER BY version DESC
    `)
    
    if (migrations.rows.length === 0) {
      console.log('📋 没有迁移记录')
      return
    }
    
    console.log('📋 迁移历史:')
    console.log('─'.repeat(100))
    console.log('版本'.padEnd(20) + '状态'.padEnd(8) + '执行时间'.padEnd(25) + '耗时(ms)'.padEnd(10) + '描述')
    console.log('─'.repeat(100))
    
    migrations.rows.forEach(row => {
      const status = row.success ? '✅ 成功' : '❌ 失败'
      const executedAt = row.executed_at.toLocaleString('zh-CN')
      const executionTime = row.execution_time_ms ? `${row.execution_time_ms}ms` : 'N/A'
      
      console.log(
        row.version.padEnd(20) +
        status.padEnd(8) +
        executedAt.padEnd(25) +
        executionTime.padEnd(10) +
        row.description
      )
    })
    
    console.log('─'.repeat(100))
    
    // 统计信息
    const stats = await pool.query(`
      SELECT 
        COUNT(*) as total_migrations,
        COUNT(CASE WHEN success = true THEN 1 END) as successful_migrations,
        COUNT(CASE WHEN success = false THEN 1 END) as failed_migrations,
        MAX(executed_at) as last_migration
      FROM schema_migrations
    `)
    
    const stat = stats.rows[0]
    console.log('\n📊 统计信息:')
    console.log(`   总迁移数: ${stat.total_migrations}`)
    console.log(`   成功: ${stat.successful_migrations}`)
    console.log(`   失败: ${stat.failed_migrations}`)
    console.log(`   最后迁移: ${stat.last_migration ? stat.last_migration.toLocaleString('zh-CN') : 'N/A'}`)
    
    // 检查数据库表状态
    console.log('\n🗄️  数据库表状态:')
    const tables = await pool.query(`
      SELECT 
        schemaname,
        tablename,
        tableowner
      FROM pg_tables 
      WHERE schemaname = 'public'
      ORDER BY tablename
    `)
    
    tables.rows.forEach(table => {
      console.log(`   📋 ${table.tablename}`)
    })
    
  } catch (error) {
    console.error('❌ 查看迁移状态失败:', error.message)
  } finally {
    await pool.end()
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  showMigrationStatus()
}

module.exports = { showMigrationStatus }
